﻿CREATE PROCEDURE [util].[ConfigOperators]
@XmlData TEXT
AS
BEGIN
	DECLARE @docHandle int
	EXEC sp_xml_preparedocument @docHandle OUTPUT, @XmlData

	-- ===============================================
	-- Add/Update Operator Details
	-- ===============================================
	DECLARE C_OPS CURSOR FOR(
		SELECT * FROM OPENXML(@docHandle, N'/Root/Operator') 
		WITH (
			[Name] sysname
			,[Enabled] tinyint
			,[EmailAddress] nvarchar(100)
			,[PagerAddress] nvarchar(100)
			,[WeekDayPagerStartTime] int
			,[WeekDayPagerEndTime] int
			,[SaturdayPagerStartTime] int
			,[SaturdayPagerEndTime] int
			,[SundayPagerStartTime] int
			,[SundayPagerEndTime] int
			,[PagerDays] tinyint
			,[NetsendAddress] nvarchar(100)
		)
	)
	OPEN C_OPS
	DECLARE @Name sysname ,@Enabled tinyint ,@EmailAddress nvarchar(100), @PagerAddress nvarchar(100)
		,@WeekDayPagerStartTime int, @WeekDayPagerEndTime int, @SaturdayPagerStartTime int, @SaturdayPagerEndTime int
		,@SundayPagerStartTime int, @SundayPagerEndTime int, @PagerDays tinyint, @NetsendAddress nvarchar(100)

	FETCH NEXT FROM C_OPS INTO @Name, @Enabled, @EmailAddress, @PagerAddress
		,@WeekDayPagerStartTime, @WeekDayPagerEndTime, @SaturdayPagerStartTime, @SaturdayPagerEndTime
		,@SundayPagerStartTime, @SundayPagerEndTime, @PagerDays, @NetsendAddress

	WHILE(@@FETCH_STATUS = 0)
	BEGIN
		IF NOT EXISTS(SELECT * FROM msdb.dbo.sysoperators WHERE ([name] = @Name))
			BEGIN
				EXEC msdb.dbo.sp_add_operator @name = @Name
			END

		EXEC msdb.dbo.sp_update_operator @name = @Name
			,@enabled = @Enabled
			,@email_address = @EmailAddress
			,@pager_address = @PagerAddress
			,@weekday_pager_start_time = @WeekDayPagerStartTime
			,@weekday_pager_end_time = @WeekDayPagerEndTime
			,@saturday_pager_start_time = @SaturdayPagerStartTime
			,@saturday_pager_end_time = @SaturdayPagerEndTime
			,@sunday_pager_start_time = @SundayPagerStartTime
			,@sunday_pager_end_time = @SundayPagerEndTime
			,@pager_days = @PagerDays
			,@netsend_address = @NetsendAddress

		FETCH NEXT FROM C_OPS INTO @Name, @Enabled, @EmailAddress, @PagerAddress
			,@WeekDayPagerStartTime, @WeekDayPagerEndTime, @SaturdayPagerStartTime, @SaturdayPagerEndTime
			,@SundayPagerStartTime, @SundayPagerEndTime, @PagerDays, @NetsendAddress
	END
	CLOSE C_OPS
	DEALLOCATE C_OPS

	EXEC sp_xml_removedocument @docHandle

	RETURN(@@ERROR)

END